package jdbc.test.oracle.dm;

import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics2D;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.imageio.ImageIO;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
///
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Dmtest_1 {
	public static void mainoracle() throws ClassNotFoundException,SQLException{
		String url = "jdbc:oracle:thin:@192.168.1.245:1521:orcl";
		String user = "zabbix";
		String password = "zabbix";
		Class.forName("oracle.jdbc.driver.OracleDriver"); //注册驱动
		
		Connection connection = null;
		connection = DriverManager.getConnection(url,user,password);
		
		String sql = "select user from dual";
		
		Statement stmt = connection.createStatement();
		ResultSet rs0 = stmt.executeQuery(sql);
		pResultSet(rs0);
		rs0.close();
		int rs2 = stmt.executeUpdate(sql);  // # 返回的是行数
		boolean rs3 = stmt.execute(sql); 
		
		System.out.println(connection);
		System.out.println(rs2);
		System.out.println(rs3);
		
		connection.close();
	}
	
	
	public static void maindm_20171101() throws ClassNotFoundException,SQLException{
		//String url = "jdbc:dm://127.0.0.1?compatibleMode=oracle";//ComOra=1  → 不可以用  compatibleMode=oracle → 可以用
		//1502的版本用下面这个  //而且不能用  新版本的 jdbc驱动
		String url = "jdbc:dm://127.0.0.1?ComOra=1";//  → 不可以用  compatibleMode=oracle → 可以用

		String user = "SYSDBA";
		String password = "SYSDBA";
		Class.forName("dm.jdbc.driver.DmDriver");//注册驱动
		
		Connection connection = null;
		connection = DriverManager.getConnection(url,user,password);
		
		String sql = "select * from v$version";
		//String sql = "create table test10086(v1 int)";
		
		Statement stmt = connection.createStatement();
		ResultSet rs0 = stmt.executeQuery(sql);
		pResultSet(rs0);
		rs0.close();
		boolean rs3 =false;
		try{
		rs3 = stmt.execute("drop table test_0;");  if(!rs3){System.out.println("表test_0 drop 成功");};
		}catch(SQLException s){}
		rs3 = stmt.execute("create table test_0 as select 1 v1;");  if(!rs3){System.out.println("表test_0 create 成功");};
		try{
		rs3 = stmt.execute("drop table test_1;");  if(!rs3){System.out.println("表test_1 drop 成功");};
		}catch(SQLException s){}
		rs3 = stmt.execute("create table test_1(v1 clob,v2 int);"); if(!rs3){System.out.println("表test_1 create 成功");};
		/*对 test_1的处理-->begin*/
		String sql1 = "insert into test_1 select ?,v1 from test_0;";
		PreparedStatement pstmt = connection.prepareStatement(sql1);
		// 设置大字段参数
		pstmt.setString(1,GetImageStr());
		if(1==2){
		try {
		// 创建一个图片用于插入大字段
		String filePath = "D:\\OneDirver\\OneDrive\\work20171020.png";
		File file = new File(filePath);
		InputStream in = new BufferedInputStream(new FileInputStream(file));
		
		pstmt.setBinaryStream(1, in, (int) file.length());
		} catch (FileNotFoundException e) {
		System.out.println(e.getMessage());
		// 如果没有图片设置为 NULL
		pstmt.setNull(1, java.sql.Types.BINARY);
		} catch (IOException e) {
		System.out.println(e.getMessage());
		}
		}
		// 执行语句
		pstmt.executeUpdate();
		// 关闭语句
		pstmt.close();
		/*对 test_1的处理<--end*/
		System.out.println(connection);
		//System.out.println(rs3+"13");
		
		connection.close();
	}
	
	public static void maindm_insert_row_slow_20171108(String ipport) throws ClassNotFoundException,SQLException{
		//String url = "jdbc:dm://127.0.0.1?compatibleMode=oracle";//ComOra=1  → 不可以用  compatibleMode=oracle → 可以用
		//1502的版本用下面这个  //而且不能用  新版本的 jdbc驱动
		String url = "jdbc:dm://"+ipport+"?ComOra=1&autoCommit=0";//  → 不可以用  compatibleMode=oracle → 可以用

		String user = "SYSDBA";
		String password = "SYSDBA";
		Class.forName("dm.jdbc.driver.DmDriver");//注册驱动
		
		Connection connection = null;
		connection = DriverManager.getConnection(url,user,password);
		
		String sql = "select * from v$version";
		//String sql = "create table test10086(v1 int)";
		
		Statement stmt = connection.createStatement();
		ResultSet rs0 = stmt.executeQuery(sql);
		pResultSet(rs0);
		rs0.close();
		boolean rs3 =false;
		try{
		rs3 = stmt.execute("drop table test_0;");  if(!rs3){System.out.println("表test_0 drop 成功");};
		}catch(SQLException s){}
		rs3 = stmt.execute("create table test_0 (v1 int,v2 varchar(300),v3 number);");  if(!rs3){System.out.println("表test_0 create 成功");};
		//try{
		//rs3 = stmt.execute("drop table test_1;");  if(!rs3){System.out.println("表test_1 drop 成功");};
		//}catch(SQLException s){}
		//rs3 = stmt.execute("create table test_1(v1 clob,v2 int);"); if(!rs3){System.out.println("表test_1 create 成功");};
		/*对 test_1的处理-->begin*/
		//String sql1 = "insert into test_1 select ?,v1 from test_0;";
		String sql1 = "insert into test_0 values(?,?,?);";
		PreparedStatement pstmt = connection.prepareStatement(sql1);
		
		long start_1_Time, start_2_Time;//记录执行时间
		start_1_Time = System.currentTimeMillis();
		int i = 5000;
		int j= 7000000/5000;
		while(i>0){
		pstmt.setLong(1, (long) (Math.random()*1000000));
		pstmt.setString(2, getRandomString(300));
		pstmt.setLong(3, (long) (Math.random()*1000000));
		// 执行语句
		pstmt.executeUpdate();
		
		i--;
		if(0==i){
			start_2_Time = System.currentTimeMillis();
			System.out.println("第"+(7000000/5000-j+1)+"次插入5000行: time is "+(start_2_Time-start_1_Time) +" 单位 ms");
			start_1_Time = System.currentTimeMillis();
			connection.commit();
			i= 5000;
			j--;
			if(0==j){
				break;
			}
		}
		}//循环插入 700w随机数据，并5000一提交
		
		
		// 关闭语句
		pstmt.close();
		/*对 test_1的处理<--end*/
		System.out.println(connection);
		//System.out.println(rs3+"13");
		
		connection.close();
	}
	
	public static void maindm_insert_references(String ipport) throws ClassNotFoundException,SQLException{
		//String url = "jdbc:dm://127.0.0.1?compatibleMode=oracle";//ComOra=1  → 不可以用  compatibleMode=oracle → 可以用
		//1502的版本用下面这个  //而且不能用  新版本的 jdbc驱动
		String url = "jdbc:dm://"+ipport+"?ComOra=1&autoCommit=0";//  → 不可以用  compatibleMode=oracle → 可以用

		String user = "SYSDBA";
		String password = "SYSDBA";
		Class.forName("dm.jdbc.driver.DmDriver");//注册驱动
		
		Connection connection = null;
		connection = DriverManager.getConnection(url,user,password);
		
		String sql = "select * from v$version";
		//String sql = "create table test10086(v1 int)";
		
		Statement stmt = connection.createStatement();
		ResultSet rs0 = stmt.executeQuery(sql);
		pResultSet(rs0);
		rs0.close();
		boolean rs3 =false;
		try{
		rs3 = stmt.execute("drop table test_0;");  if(!rs3){System.out.println("表test_0 drop 成功");};
		}catch(SQLException s){}
		rs3 = stmt.execute("create table test_0 (v1 int,v2 varchar(300),v3 number);");  if(!rs3){System.out.println("表test_0 create 成功");};
		
//		drop table td_cos;drop table td_provider_cos;
//		create table td_cos(id int identity(1,1) primary key,cos_name varchar,is_system_cos varchar);
//		create table td_provider_cos(cos_id int,provider_id int,num_of_classes varchar,CONSTRAINT pkcos--建立表时，直接创建主键约束
//		FOREIGN KEY (cos_id) REFERENCES td_cos(id) );
		
		
		String sql1 = ""
                + "DECLARE BEGIN "
                + "INSERT INTO td_cos (cos_name,is_system_cos) values(?,?) RETURNING id INTO ?;"
                + "END;";
		//PreparedStatement pstmt = connection.prepareStatement(sql1);
		CallableStatement pstmt = connection.prepareCall(sql1);
		long start_1_Time, start_2_Time;//记录执行时间
		start_1_Time = System.currentTimeMillis();
		int i = 5000;
		int j= 7000000/5000;
		while(i>0){
		pstmt.setString(1, getRandomString(30));
		pstmt.setString(2, getRandomString(30));
		//pstmt.setLong(3, (long) (Math.random()*1000000));
		pstmt.registerOutParameter(3, java.sql.Types.INTEGER);
		// 执行语句
		pstmt.executeUpdate();
		String x =  pstmt.getString(3);
		
		String sql2 = ""
                + "DECLARE BEGIN "
                + "insert into td_provider_cos values(?,?,?);"
                + "END;";
		PreparedStatement pstmt1 = connection.prepareCall(sql2);
		pstmt1.setString(1, x);
		pstmt1.setLong(2, (long) (Math.random()*1000000));
		pstmt1.setLong(3, (long) (Math.random()*1000000));
		pstmt1.executeUpdate();
		pstmt1.close();
		//that colse pstmt ,error for 无效的序列号
		i--;
		if(0==i){
			start_2_Time = System.currentTimeMillis();
			System.out.println("第"+(7000000/5000-j+1)+"次插入5000行: time is "+(start_2_Time-start_1_Time) +" 单位 ms");
			start_1_Time = System.currentTimeMillis();
			connection.commit();
			i= 5000;
			j--;
			if(0==j){
				break;
			}
		}
		}//循环插入 700w随机数据，并5000一提交
		
		
		// 关闭语句
		
		//pstmt1.close();

		/*对 test_1的处理<--end*/
		System.out.println(connection);
		//System.out.println(rs3+"13");
		
		connection.close();
	}
		
		//获取指定位数的随机字符串(包含小写字母、大写字母、数字,0<length)
		public static String getRandomString(int length) {
		    //随机字符串的随机字符库
		    String KeyString = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
		    StringBuffer sb = new StringBuffer();
		    int len = KeyString.length();
		    for (int i = 0; i < length; i++) {
		       sb.append(KeyString.charAt((int) Math.round(Math.random() * (len - 1))));
		    }
		    return sb.toString();
		}
	
    public static String GetImageStr() {
        // 将图片文件转化为字节数组字符串，并对其进行Base64编码处理
        String imgFile = "D:\\OneDirver\\OneDrive\\work20171020.png";
        //String imgFile = "D:\\360安全浏览器下载\\ms.odbc-oracle odbc 2.0 手册 .pdf";
        InputStream in = null;
        byte[] data = null;
        // 读取图片字节数组
        try {
            in = new FileInputStream(imgFile);
            data = new byte[in.available()];
            in.read(data);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 对字节数组Base64编码
        BASE64Encoder encoder = new BASE64Encoder();
        // 返回Base64编码过的字节数组字符串
        return encoder.encode(data);
    }
	
	public static void pResultSet(ResultSet rset) throws ClassNotFoundException,SQLException{
		System.out.println ("begin---->");
		while (rset.next ())
		      System.out.println ("User name is " + rset.getString (1));
		System.out.println ("<----end");
	}

	public static void maindm() throws ClassNotFoundException,SQLException{
		//String url = "jdbc:dm://127.0.0.1?compatibleMode=oracle";//ComOra=1  → 不可以用  compatibleMode=oracle → 可以用
		//1502的版本用下面这个  //而且不能用  新版本的 jdbc驱动
		String url = "jdbc:dm://127.0.0.1";//  → 不可以用  compatibleMode=oracle → 可以用
		
		String user = "SYSDBA";
		String password = "SYSDBA";
		Class.forName("dm.jdbc.driver.DmDriver");//注册驱动
		
		Connection connection = null;
		connection = DriverManager.getConnection(url,user,password);
		
		String sql = "select * from v$version";
		//String sql = "create table test10086(v1 int)";
		
		Statement stmt = connection.createStatement();
		ResultSet rs0 = stmt.executeQuery(sql);
		pResultSet(rs0);
		rs0.close();
		int rs2 = stmt.executeUpdate(sql); //语句类型不匹配 -- 用兼容模式可以支持  # 返回的是行数
		boolean rs3 = stmt.execute(sql); 
		
		System.out.println(connection);
		System.out.println(rs2+"12");
		System.out.println(rs3);
		
		connection.close();
	}
	
	public static void main(String[] args) throws ClassNotFoundException,SQLException{
		//mainoracle();
		//maindm();
		//maindm_20171101();
		maindm_insert_references("127.0.0.1:5236");//maindm_insert_row_slow_20171108("192.168.3.90:5236");//maindm_insert_row_slow_20171108("52362");
	}

}
